﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Data.OleDb;

namespace GSLMS.DAL.Oracle
{
    public class OracleHelper:PY.Data.OracleHelper
    {
        public  OracleHelper() { } 

        /// <summary>
        /// DataTable过滤
        /// </summary>
        /// <param name="dt">要过滤的DataTable数据表</param>
        /// <param name="searchString">要查询的条件，如：“ID=0”等</param>
        /// <param name="sortString">要排序的条件，如：“ID DESC”等</param>
        /// <returns></returns>
        public static DataTable DataTableFileter(DataTable dt, string searchString, string sortString)
        {
           
            DataTable newDt = new DataTable();


            if (dt != null && dt.Rows.Count > 0)
            {
                newDt = dt.Clone();

                DataRow[] dr = dt.Select(searchString, sortString);


                for (int i = 0; i < dr.Length; i++)
                    newDt.ImportRow((DataRow)dr[i]);
            }

            return newDt;
        }

        /// <summary>
        /// 消除重复列，返回消除重复列后的DataTable返回（只返回要消除列的列表）
        /// </summary>
        /// <param name="sourceDataTable">要消除重复列的数据表</param>
        /// <param name="fieldName">要消除的列名</param>
        /// <returns></returns>
        public static DataTable DistinctDataTableColumn(DataTable sourceDataTable, string fieldName)
        {

            string tableName = "new" + sourceDataTable.TableName;

            DataTable dt = new DataTable(tableName);
            dt.Columns.Add(fieldName, sourceDataTable.Columns[fieldName].DataType);
            object lastValue = null;
            foreach (DataRow dr in sourceDataTable.Select("", fieldName))
            {
                if (lastValue == null || !ColumnEqual(lastValue, dr[fieldName]))
                {
                    lastValue = dr[fieldName];
                    dt.Rows.Add(new object[] { lastValue });
                }
            }

            return dt;
        }

        /// <summary>
        /// 分页提取数据
        /// </summary>
        /// <param name="tblName">查询的表名</param>
        /// <param name="strFields">查询的字段</param>
        /// <param name="strSort">排序的字段</param>
        /// <param name="pageSize">分页的容量，默认为20条/页</param>
        /// <param name="pageIndex">要取的页码，默认为第1页</param>
        /// <param name="countFlag">是否统计总数据</param>
        /// <param name="sqlWhere">查询条件，不要写Where</param>
        /// <param name="totalCount">输出值，记录总数</param>
        /// <returns></returns>
        public static DataTable GetPageData(string tblName, string strFields, string strSort, int pageSize, int pageIndex,int countFlag, string sqlWhere, out int totalCount)
        {
            DataTable dt = null;
            OracleParameter[] prams = { new OracleParameter("tblName",tblName), 
                                        new OracleParameter("strFields",strFields),
                                        new OracleParameter("strSort",strSort),
                                        new OracleParameter("pageSize",pageSize),
                                        new OracleParameter("pageIndex",pageIndex),
                                        new OracleParameter("strWhere",sqlWhere),
                                        new OracleParameter("recordCount",OracleType.Int32),
                                        new OracleParameter("selRecords",OracleType.Cursor),
                                        new OracleParameter("countflag",countFlag)
                                      };


            totalCount = 0;

            prams[0].Direction = ParameterDirection.Input;
            prams[1].Direction = ParameterDirection.Input;
            prams[2].Direction = ParameterDirection.Input;
            prams[3].Direction = ParameterDirection.Input;
            prams[4].Direction = ParameterDirection.Input;
            prams[5].Direction = ParameterDirection.Input;
            prams[6].Direction = ParameterDirection.Output;
            prams[7].Direction = ParameterDirection.Output;
            prams[8].Direction = ParameterDirection.Input;

            DataSet ds = null;
            ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_util.proc_DataPage2", prams);
            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];
            if (prams[6].Value != DBNull.Value)
                totalCount = ToInt(prams[6].Value.ToString());

            return dt;
        }


        public static string GetWebConfigAppSettingsNodeValue(string nodeName)
        {
            string retStr = "";
            try
            {
                retStr = System.Configuration.ConfigurationManager.AppSettings[nodeName].ToString();
            }
            catch (Exception ex)
            {
                ex.ToString();
            }

            return retStr;
        }

        /// <summary>
        ///  string to decimal
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static decimal ToDecimal(string str)
        {
            decimal dec = 0;
            try
            {
                dec = Convert.ToDecimal(str.Trim());
            }
            catch (Exception ex)
            {
                ex.ToString();
            }

            return dec;
        }

        /// <summary>
        ///  string to decimal
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static decimal ToDecimal(object str)
        {
            decimal dec = 0;
            try
            {
                dec = Convert.ToDecimal(str.ToString().Trim());
            }
            catch (Exception ex)
            {
                ex.ToString();
            }

            return dec;
        }

        /// <summary>
        /// string To long(Int64)
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static long ToLong(string str)
        {
            long num = 0;

            try
            {
                num = Convert.ToInt64(str.Trim());
            }
            catch (Exception ex)
            {
                ex.ToString();
            }


            return num;
        }

        /// <summary>
        /// string to int
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static int ToInt(string str)
        {
            int num = 0;
            try
            {
                num = Convert.ToInt32(str.Trim());
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            return num;
        }

        /// <summary>
        /// 转换Int32
        /// </summary>
        /// <param name="str">要转换的字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <returns></returns>
        public static int ToInt(string str, int defValue)
        {
            bool flag = false;
            int outInt = defValue;
            if (str != null && str.Trim() != "")
            {
                flag = Int32.TryParse(str, out outInt);
                if (flag)
                    return outInt;
                else
                    return defValue;
            }

            return outInt;
        }

        /// <summary>
        /// 转换成decimal
        /// </summary>
        /// <param name="str">字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <returns></returns>
        public static decimal ToDecimal(string str, decimal defValue)
        {
            bool flag = false;
            decimal outDecimal = defValue;
            if (str != null && str.Trim() != "")
            {
                flag = decimal.TryParse(str, out outDecimal);
                if (flag)
                    return outDecimal;
                else
                    return defValue;
            }

            return outDecimal;
        }

        /// <summary>
        /// 转换成decimal
        /// </summary>
        /// <param name="str">字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <param name="decimals">精度</param>
        /// <returns></returns>
        public static decimal ToDecimal(string str, decimal defValue, int decimals)
        {
            bool flag = false;
            decimal outDecimal = defValue;
            if (str != null && str.Trim() != "")
            {

                flag = decimal.TryParse(str, out outDecimal);
                outDecimal = Decimal.Round(outDecimal, decimals);
                if (flag)
                    return outDecimal;
                else
                    return defValue;
            }

            return outDecimal;
        }


        /// <summary>
        /// 转换成decimal
        /// </summary>
        /// <param name="str">字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <param name="decimals">精度</param>
        /// <returns></returns>
        public static decimal ToDecimal(object str, decimal defValue, int decimals)
        {
            bool flag = false;
            decimal outDecimal = defValue;
            if (str != null && str.ToString().Trim() != "")
            {

                flag = decimal.TryParse(str.ToString(), out outDecimal);
                outDecimal = Decimal.Round(outDecimal, decimals);
                if (flag)
                    return outDecimal;
                else
                    return defValue;
            }

            return outDecimal;
        }


        /// <summary>
        /// 比较两个对象是否相等
        /// </summary>
        /// <param name="A">A对象</param>
        /// <param name="B">B对象</param>
        /// <returns></returns>
        public static bool ColumnEqual(object A, object B)
        {
            if (A == DBNull.Value && B == DBNull.Value)
                return true;
            if (A == DBNull.Value || B == DBNull.Value)
                return false;
            return A.Equals(B);
        }

        /// <summary>
        /// 从EXCEL中获取数据
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetName">工作簿名</param>
        /// <param name="FileName">文件全名</param>
        /// <returns></returns>
        public static DataTable GetExcelData(string filePath, string sheetName, string FileName)
        {
            DataSet ds = new DataSet();
            DataTable dt = null;
            string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;
            string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";
            string queryStr = "SELECT * FROM [" + sheetName + "$]";
            OleDbConnection conn03 = new OleDbConnection(connStr03);
            OleDbConnection conn07 = new OleDbConnection(connStr07);
            string fileExt = System.IO.Path.GetExtension(FileName);
            if (fileExt == ".xls")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
                myAdapter.Fill(ds);
            }
            else if (fileExt == ".xlsx")
            {
                OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
                myAdapter.Fill(ds);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }



            return dt;

            
        }

        /// <summary>
        /// 转换不安全的SQL字符
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string ReplaceUnsafeSql(string str)
        {
            return PY.Components.Utils.ReplaceUnsafeSql(str);
        }

        /// <summary>
        /// 增加缓存
        /// </summary>
        /// <param name="objID"></param>
        /// <param name="o"></param>
        /// <param name="_timeOut"></param>
        public static void AddCacheObject(string objID,object o,int _timeOut)
        {
            PY.Cache.Cache.AddObject(objID, o, _timeOut);
        }

        /// <summary>
        /// 获取缓存
        /// </summary>
        /// <param name="objID"></param>
        /// <returns></returns>
        public static object GetCachecObject(string objID)
        {
            return PY.Cache.Cache.ReturnObject(objID);
        }

        /// <summary>
        /// 移除缓存
        /// </summary>
        /// <param name="objID"></param>
        public static void RemoveCacheObject(string objID)
        {
            PY.Cache.Cache.RemoveObject(objID);

        }

  

        /// <summary>
        /// 产生随机数
        /// </summary>
        /// <param name="len"></param>
        /// <returns></returns>
        public static string CreateSalt(int len)
        { 
            char[] CharArray = { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '1', '2', '3', '4', '5', '6', '7', '8', '9' }; 
            string sCode = "";
            Random random = new Random(); 

            for (int i = 0; i < len; i++) 
            { sCode += CharArray[random.Next(CharArray.Length)]; 
            } 

            return sCode; 
        }

        /// <summary>
        /// MD5加密
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string MD5Encode(string str)
        {
            return PY.Components.MD5.Encode(str);
        }


    }
}
